#! /bin/bash

if [ -n "$2" ] ;
then
  do_date=$2
else
  do_date=`date -d '-1 day' +%F`
fi

env_config="
use dw;
SET mapreduce.job.queuename=tianyan;
SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
SET hive.exec.dynamic.partition.mode=nonstrict;"

dws_profit_company_station="
with consume as (
	select
		group_id,dt,station_id,fuel_type,sum(mc_pay_money) as consume_money
	from (
		select
			ca.group_id,owtoa.si_id as station_id,from_unixtime(owtoa.create_time, 'yyyy-MM-dd') as dt,
			if(owtoa.fuel_no < 2000,2,if(owtoa.fuel_no < 3000,1,if(owtoa.fuel_no < 4000,4,3))) as fuel_type,
			owtoa.mc_pay_money
		from (select dp.partition_dt from dw.dim_partition dp where dp.dt ='$do_date' group by dp.partition_dt ) dp
		left join dw.ods_wjy_tb_order_all owtoa on dp.partition_dt = owtoa.dt
		left join dw.ods_wjy_tb_company_accountinfo ca on owtoa.cu_id = ca.cu_id
		where  ca.dt = '$do_date'
		and  owtoa.status = 0
	) t
	group by group_id,dt,station_id,fuel_type
),
rebate as (
	select
		g2.group_id,g2.dt,g2.station_id,
		if(g2.fuel_no < 2000,2,if(g2.fuel_no < 3000,1,if(g2.fuel_no < 4000,4,3))) as fuel_type,
		sum(station_rebate_money) as  rebate_money
	from (
		select
			f2.order_id,f2.station_id,f2.release_id,f2.recharge_id,f2.rule_id,f2.group_id,f2.dt,f2.fuel_no,
			if(real_station_rebate_money != 0,real_station_rebate_money,sys_station_rebate_money) as station_rebate_money
		from (
			select
				t2.order_id,t2.station_id,t2.release_id,t2.recharge_id,t2.rule_id,t2.group_id,t2.dt,t2.fuel_no,
				sum(if(t2.rebate_type = 2,t2.rebate_money,0))  as real_station_rebate_money,
				sum(if(t2.rebate_type = 1,t2.rebate_money,0))  as sys_station_rebate_money
			from (
				select
					dsrd.group_id,dp.partition_dt as dt,dsrd.station_id,dsrd.order_id,dsrd.fuel_no,dsrd.release_id,dsrd.recharge_id,dsrd.rule_id,dsrd.rebate_type,dsrd.rebate_money,
					ROW_NUMBER() over(partition by order_id,release_id,recharge_id,rule_id,rebate_type order by dsrd.create_time desc ) as rank_num
				from (select dp.partition_dt from dw.dim_partition dp where dp.dt ='$do_date' group by dp.partition_dt )  dp
				left join dwd_station_rebate_detail dsrd  on dp.partition_dt = dsrd.odt
			) t2
			where t2.rank_num = 1
			group by  t2.order_id,t2.station_id,t2.release_id,t2.recharge_id,t2.rule_id,t2.group_id,t2.dt,t2.fuel_no
		) f2
	) g2
	group by g2.group_id,g2.dt,g2.station_id,g2.fuel_no
)
insert overwrite table dws_rebate_company_station partition(dt)
select
	nvl(con.group_id,reb.group_id) as group_id,
	nvl(con.station_id,reb.station_id) as station_id,
	nvl(con.fuel_type,reb.fuel_type) as fuel_type,
	con.consume_money as amt_real,
	reb.rebate_money as amt_rebate,
	if(dp.partition_dt is null,if(con.dt is null,reb.dt,con.dt),dp.partition_dt)
from (select dp.partition_dt from dw.dim_partition dp where dp.dt ='$do_date' group by dp.partition_dt ) dp
left join consume con on dp.partition_dt = con.dt
FULL join rebate reb on con.group_id = reb.group_id and dp.partition_dt = reb.dt and reb.station_id = con.station_id and reb.fuel_type = con.fuel_type;
"

dws_profit_company="
with recharge as (
	select
		ca.group_id,
		dp.partition_dt,
		sum(dtml.money) as recharge,
		sum(if(ca.category = 3,0,dtml.money)) as oil_recharge,
		sum(if(ca.category = 3,dtml.money,0)) as lng_recharge
	from (select dp.partition_dt from dw.dim_partition dp where dp.dt ='$do_date' group by dp.partition_dt) dp
	left join dw.dwd_tb_ma_log dtml on dp.partition_dt = dtml.dt
	left join dw.ods_wjy_tb_audit owta on dtml.mal_id = owta.cache_id
	left join dw.ods_wjy_tb_company_accountinfo ca on dtml.cu_id = ca.cu_id
	where dtml.type = 1
  	and dtml.status = 0
	and owta.type = 2
	and owta.status = 2
	and ca.dt = '$do_date'
	and owta.dt = '$do_date'
	group by ca.group_id,dp.partition_dt
),
consume as (
	select
		ca.group_id,dp.partition_dt,
		sum(owtoa.mc_pay_money) as consume_mc_total,
		sum(if(owtoa.fuel_no < 2000,owtoa.mc_pay_money,0)) as consume_mc_gasoline,
		sum(if(owtoa.fuel_no >= 2000 and owtoa.fuel_no < 3000,owtoa.mc_pay_money,0)) as consume_mc_oil,
		sum(if(owtoa.fuel_no >= 3000 and owtoa.fuel_no < 4000,owtoa.mc_pay_money,0)) as consume_mc_other,
		sum(if(owtoa.fuel_no >= 4000,owtoa.mc_pay_money,0)) as consume_mc_lng,
		sum(owtoa.c_pay_money) as consume_c_total,
		sum(if(owtoa.fuel_no >= 2000 and owtoa.fuel_no < 3000,owtoa.c_pay_money,0)) as consume_c_oil,
		sum(if(owtoa.fuel_no >= 4000,owtoa.c_pay_money,0)) as consume_c_lng
	from (select dp.partition_dt from dw.dim_partition dp where dp.dt ='$do_date' group by dp.partition_dt) dp
	left join dw.ods_wjy_tb_order_all owtoa on dp.partition_dt = owtoa.dt
	left join dw.ods_wjy_tb_company_accountinfo ca on owtoa.cu_id = ca.cu_id
	where ca.dt = '$do_date'
	and  owtoa.status = 0
	group by ca.group_id,dp.partition_dt
),
rebate as (
    select
    	t.group_id,t.dt as partition_dt,
		sum(t.station_rebate_money) as station_profit_money,
		sum(if(t.fuel_no <  2000,t.station_rebate_money,0)) as station_profit_gasoline,
		sum(if(t.fuel_no >= 2000 and t.fuel_no < 3000,t.station_rebate_money,0)) as station_profit_oil,
		sum(if(t.fuel_no >= 3000 and t.fuel_no < 4000,t.station_rebate_money,0)) as station_profit_other,
		sum(if(t.fuel_no >= 4000,t.station_rebate_money,0)) as station_profit_lng,
		sum(t.company_rebate_money) as company_rebate_money,
		sum(if(t.fuel_no >= 2000 and t.fuel_no < 3000,t.company_rebate_money,0)) as company_rebate_oil,
		sum(if(t.fuel_no >= 4000,t.company_rebate_money,0)) as company_rebate_lng,
		sum(t.station_rebate_money) - sum(t.company_rebate_money) as rebate_diff,
		sum(if(t.fuel_no >= 2000 and t.fuel_no < 3000,t.station_rebate_money,0)) - sum(if(t.fuel_no >= 2000 and t.fuel_no < 3000,t.company_rebate_money,0)) as rebate_oil_diff,
		sum(if(t.fuel_no >= 4000,t.station_rebate_money,0)) - sum(if(t.fuel_no >= 4000,t.company_rebate_money,0)) as rebate_lng_diff
    from (
		select
			s1.group_id,s1.dt,s1.order_id,s1.fuel_no,s1.company_rebate_money,s2.station_rebate_money
		from (
			select
				g1.group_id,g1.dt,g1.order_id,g1.fuel_no,sum(company_rebate_money) as company_rebate_money
			from (
				select
					f1.order_id,f1.release_id,f1.recharge_id,f1.rule_id,f1.group_id,f1.dt,f1.fuel_no,
					if(real_company_rebate_money != 0,real_company_rebate_money,sys_company_rebate_money) as company_rebate_money
				from (
					select
						t1.order_id,t1.release_id,t1.recharge_id,t1.rule_id,t1.group_id,t1.dt,t1.fuel_no,
						sum(if(t1.rebate_type = 2,t1.rebate_money,0))  as real_company_rebate_money,
						sum(if(t1.rebate_type = 1,t1.rebate_money,0))  as sys_company_rebate_money
					from (
						select
							dcrd.group_id,dp.partition_dt as dt,dcrd.order_id,dcrd.fuel_no,dcrd.release_id,dcrd.recharge_id,dcrd.rule_id,dcrd.rebate_type,dcrd.rebate_money,
							ROW_NUMBER() over(partition by order_id,release_id,recharge_id,rule_id,rebate_type order by dcrd.create_time desc ) as rank_num
						from (select dp.partition_dt from dw.dim_partition dp where dp.dt ='$do_date' group by dp.partition_dt) dp
						left join dwd_company_rebate_detail dcrd  on dp.partition_dt = dcrd.dt
					) t1
					where t1.rank_num = 1
					group by  t1.order_id,t1.release_id,t1.recharge_id,t1.rule_id,t1.group_id,t1.dt,t1.fuel_no
				) f1
			) g1 group by g1.group_id,g1.dt,g1.order_id,g1.fuel_no
		) s1 left join (
			select
				g2.group_id,g2.dt,g2.order_id,g2.fuel_no,sum(station_rebate_money) as station_rebate_money
			from (
				select
					f2.order_id,f2.release_id,f2.recharge_id,f2.rule_id,f2.group_id,f2.dt,f2.fuel_no,
					if(real_station_rebate_money != 0,real_station_rebate_money,sys_station_rebate_money) as station_rebate_money
				from (
					select
						t2.order_id,t2.release_id,t2.recharge_id,t2.rule_id,t2.group_id,t2.dt,t2.fuel_no,
						sum(if(t2.rebate_type = 2,t2.rebate_money,0))  as real_station_rebate_money,
						sum(if(t2.rebate_type = 1,t2.rebate_money,0))  as sys_station_rebate_money
					from (
						select
							dsrd.group_id,dp.partition_dt as dt,dsrd.order_id,dsrd.fuel_no,dsrd.release_id,dsrd.recharge_id,dsrd.rule_id,dsrd.rebate_type,dsrd.rebate_money,
							ROW_NUMBER() over(partition by order_id,release_id,recharge_id,rule_id,rebate_type order by dsrd.create_time desc ) as rank_num
						from (select dp.partition_dt from dw.dim_partition dp where dp.dt ='$do_date' group by dp.partition_dt) dp
						left join dwd_station_rebate_detail dsrd  on dp.partition_dt = dsrd.odt
					) t2
					where t2.rank_num = 1
					group by  t2.order_id,t2.release_id,t2.recharge_id,t2.rule_id,t2.group_id,t2.dt,t2.fuel_no
				) f2
			) g2 group by g2.group_id,g2.dt,g2.order_id,g2.fuel_no
		) s2 on s1.order_id = s2.order_id
	) t group by t.group_id,t.dt
),
company_rule as (
	select
		t.group_id,t.dt as partition_dt,CONCAT_WS(',',collect_set(t.rule_name)) as rule_names
	from (
		select
			s1.group_id,s1.dt,drr.rule_name
		from (
			select
				t1.group_id,t1.dt,t1.rule_id
			from (
				select
					dcrd.group_id,dp.partition_dt as dt,dcrd.rule_id,
					ROW_NUMBER() over(partition by order_id,release_id,recharge_id,rule_id,rebate_type order by dcrd.create_time desc ) as rank_num
				from (select dp.partition_dt from dw.dim_partition dp where dp.dt ='$do_date' group by dp.partition_dt ) dp
				left join dwd_company_rebate_detail dcrd  on dp.partition_dt = dcrd.dt
			) t1
			where t1.rank_num = 1
			group by t1.group_id,t1.dt,t1.rule_id
		) s1 left join dw.dim_rebate_record drr on s1.rule_id = drr.id
		group by s1.group_id,s1.dt,drr.rule_name
	) t group by t.group_id,t.dt
)
insert overwrite table dws_rebate_company partition(dt)
select
	if(rec.group_id is null,if(reb.group_id is null,con.group_id,reb.group_id),rec.group_id) as group_id,
	nvl(cr.rule_names,'') as rebate_rule,
	nvl(rec.recharge,0) as recharge,
	nvl(rec.oil_recharge,0) as oil_recharge,
	nvl(rec.lng_recharge,0) as lng_recharge,
	nvl(con.consume_mc_total,0) as consume_mc_total,
	nvl(con.consume_mc_oil,0) as consume_mc_oil,
	nvl(con.consume_mc_lng,0) as consume_mc_lng,
	nvl(con.consume_mc_gasoline,0) as consume_mc_gasoline,
	nvl(con.consume_mc_other,0) as consume_mc_other,
	nvl(reb.station_profit_money,0) as station_profit_money,
	nvl(reb.station_profit_oil,0) as station_profit_oil,
	nvl(reb.station_profit_lng,0) as station_profit_lng,
	nvl(reb.station_profit_gasoline,0) as station_profit_gasoline,
	nvl(reb.station_profit_other,0) as station_profit_other,
	nvl(reb.company_rebate_money,0) as company_rebate_money,
	nvl(reb.company_rebate_oil,0) as company_rebate_oil,
	nvl(reb.company_rebate_lng,0) as company_rebate_lng,
	nvl(con.consume_mc_total - con.consume_c_total + reb.rebate_diff,0) as amt_real_profit,
	nvl(con.consume_mc_oil - con.consume_c_oil + reb.rebate_oil_diff,0) as amt_oil_real_profit,
	nvl(con.consume_mc_lng - con.consume_c_lng + reb.rebate_lng_diff,0) as amt_lng_real_profit,
	if(dp.partition_dt is null,if(rec.partition_dt is null,if(reb.partition_dt is null,if(con.partition_dt is null,cr.partition_dt,con.partition_dt),reb.partition_dt),rec.partition_dt),dp.partition_dt)
from (select dp.partition_dt from dw.dim_partition dp where dp.dt ='$do_date' group by dp.partition_dt ) dp
left join recharge rec on dp.partition_dt = rec.partition_dt
FULL join rebate reb on rec.group_id = reb.group_id and dp.partition_dt = reb.partition_dt
FULL join consume con on rec.group_id = con.group_id and dp.partition_dt = con.partition_dt
FULL join company_rule cr on rec.group_id = cr.group_id and dp.partition_dt = cr.partition_dt;
"

dws_profit_company_p="
insert overwrite table dws_rebate_company_p partition(dt)
select
	dpc.p_group_name,
	CONCAT_WS(',',collect_set(distinct drc.rebate_rule)) ,
	sum(drc.amt_recharge),
	sum(drc.amt_oil_recharge),
	sum(drc.amt_lng_recharge),
	sum(drc.amt_consume),
	sum(drc.amt_oil_consume),
	sum(drc.amt_lng_consume),
	sum(drc.amt_gasoline_consume),
	sum(drc.amt_other_consume),
	sum(drc.amt_profit),
	sum(drc.amt_oil_profit),
	sum(drc.amt_lng_profit),
	sum(drc.amt_gasoline_profit),
	sum(drc.amt_other_profit),
	sum(drc.amt_rebate),
	sum(drc.amt_oil_rebate),
	sum(drc.amt_lng_rebate),
	sum(drc.amt_real_profit),
	sum(drc.amt_oil_real_profit),
	sum(drc.amt_lng_real_profit),
	dp.dt
from dw.dim_partition dp
left join dw.dws_rebate_company drc on dp.dt = drc.dt
left join dw.dim_profit_company dpc on drc.group_id = dpc.group_id
where dp.dt = '$do_date'
group by dpc.p_group_name,dp.dt;"

insert_into_table(){
  case $1 in
  "all")
    echo $1
    hive -e "$env_config$dws_profit_company_station$dws_profit_company$dws_profit_company_p"
    ;;
  "dws_profit_company_station")
    echo $1
    hive -e "$env_config$dws_profit_company_station"
    ;;
  "dws_profit_company")
    hive -e "$env_config$dws_profit_company"
    ;;
  "dws_profit_company_p")
    hive -e "$env_config$dws_profit_company_p"
    ;;
  esac
}

insert_into_table $1